package com.sunwayworld.basemodule.business.result.io;

import com.alibaba.fastjson.JSONObject;
import com.sunwayworld.basemodule.basedata.exporttemplate.service.LimsGenericTemplateService;
import com.sunwayworld.basemodule.business.ordertask.bean.LimsOrderTaskBean;
import com.sunwayworld.basemodule.business.ordertask.service.LimsOrderTaskService;
import com.sunwayworld.basemodule.business.result.bean.LimsResultBean;
import com.sunwayworld.basemodule.business.result.bean.LimsRunBean;
import com.sunwayworld.basemodule.business.result.bean.LimsRunMaterialBean;
import com.sunwayworld.basemodule.business.result.service.LimsResultService;
import com.sunwayworld.basemodule.business.result.service.LimsRunMaterialService;
import com.sunwayworld.basemodule.business.result.service.LimsRunService;
import com.sunwayworld.basemodule.coredata.test.bean.LimsPossibleResultBean;
import com.sunwayworld.basemodule.samplemanage.sample.bean.LimsSampleBean;
import com.sunwayworld.basemodule.samplemanage.sample.service.LimsSampleDerivativeService;
import com.sunwayworld.basemodule.samplemanage.sample.service.LimsSampleService;
import com.sunwayworld.framework.constant.Constant;
import com.sunwayworld.framework.data.page.Page;
import com.sunwayworld.framework.io.excel.utils.ExcelUtils;
import com.sunwayworld.framework.mybatis.mapper.MapperParameter;
import com.sunwayworld.framework.mybatis.mapper.MatchPattern;
import com.sunwayworld.framework.mybatis.mapper.SearchFilter;
import com.sunwayworld.framework.restful.data.RestJsonWrapperBean;
import com.sunwayworld.framework.spring.annotation.GikamBean;
import com.sunwayworld.framework.utils.BeanUtils;
import com.sunwayworld.framework.utils.DateTimeUtils;
import com.sunwayworld.framework.utils.ObjectUtils;
import com.sunwayworld.framework.utils.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.io.*;
import java.nio.file.Path;
import java.util.List;
import java.util.Map;
import java.util.Optional;
import java.util.concurrent.ConcurrentHashMap;
import java.util.stream.Collectors;

@Component(value = "LimsRunTaskListExportTemplate")
@GikamBean
public class LimsRunTaskListExportTemplate implements LimsGenericTemplateService<String> {

    @Autowired
    private LimsResultService resultService;

    @Autowired
    private LimsRunService runService;

    @Autowired
    private LimsOrderTaskService orderTaskService;

    @Autowired
    private LimsSampleService limsSampleService;

    @Autowired
    private LimsRunMaterialService limsRunMaterialService;

    @Autowired
    private LimsSampleDerivativeService limsSampleDerivativeService;

    @Override
    public void initTemplateData(Path path, String id) {
        if (StringUtils.isEmpty(id)) {
            return;
        }
        MapperParameter parameter = new MapperParameter();
        parameter.setFilter(SearchFilter.instance().match("id", id).filter(MatchPattern.EQ));
        LimsRunBean runBean = runService.selectFirst(parameter);
        try {
            InputStream in = new FileInputStream(path.toString());
            Workbook workBook = WorkbookFactory.create(in);
            Sheet sheet = workBook.getSheetAt(0);
            Map<Integer, Long> analyteMap = new ConcurrentHashMap<>();
            Map<Integer, List<LimsPossibleResultBean>> possibleMap = new ConcurrentHashMap<>();
            RestJsonWrapperBean resultWrapperBean = new RestJsonWrapperBean();
//            resultWrapperBean.setFilterValue("taskListExportShowFlag", "1");
            Page<LimsResultBean> resultColumnPage = resultService.selectResultToColumns(runBean.getId(), resultWrapperBean);
            if (!resultColumnPage.getRows().isEmpty()) {
                //补充动态列
                addResultColumns(sheet.getRow(5), workBook, resultColumnPage.getRows(), analyteMap, possibleMap);
            }

            RestJsonWrapperBean orderTaskWrapperBean = new RestJsonWrapperBean();
            orderTaskWrapperBean.setFilterValue("runId_EQ", runBean.getId());
            orderTaskWrapperBean.setFilterValue("status_SB", "logged");
            orderTaskWrapperBean.setFilterValue("queries_raw", "1");
            orderTaskWrapperBean.setFilterValue("flag_EQ", "1");

            orderTaskWrapperBean.removePageParams();
            Page<LimsOrderTaskBean> orderTaskBeanPage = orderTaskService.selectOrderTaskWithResult(orderTaskWrapperBean);

            List<Long> orderTaskIds = orderTaskBeanPage.getRows().stream().map(LimsOrderTaskBean::getId).collect(Collectors.toList());
            List<LimsResultBean> resultBeans = resultService.selectListByFilter(SearchFilter.instance().match("orderTaskId", orderTaskIds).filter(MatchPattern.OR));

            RestJsonWrapperBean selectBean = new RestJsonWrapperBean();
            selectBean.setFilterValue("useFlg_EQ", "1");
            List<LimsSampleBean> derivativeBeans = limsSampleDerivativeService.selectDerivativeInRunList(runBean.getId(), selectBean);
            if (!orderTaskBeanPage.getRows().isEmpty()) {
                //补充数据
                addInfo(sheet, workBook, orderTaskBeanPage.getRows(), analyteMap, runBean, derivativeBeans, resultBeans, possibleMap);
            }
            in.close();
            FileOutputStream outputStream = new FileOutputStream(path.toString());
            workBook.write(outputStream);
            outputStream.close();
            workBook.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    //*********************************************************************
    //私有方法
    //*********************************************************************
    private void addResultColumns(Row row, Workbook wb, List<LimsResultBean> resultColumnList, Map<Integer, Long> analyteMap, Map<Integer, List<LimsPossibleResultBean>> possibleMap) {
//        int endCellNum = row.getPhysicalNumberOfCells();
        int endCellNum = 5;
        CellStyle csForTitle = ExcelUtils.getTitleCellStyle(wb); // 子标题和列标题的样式
        csForTitle.setBorderTop(BorderStyle.THIN);
        CellStyle csForTitleRequired = ExcelUtils.getTitleCellStyle(wb);// 必填字段样式
        Font font = wb.createFont();
        font.setColor(Font.COLOR_RED);
        csForTitleRequired.setFont(font);
        csForTitleRequired.setBorderTop(BorderStyle.THIN);
        DataFormat dataFormat = wb.createDataFormat();
        csForTitle.setDataFormat(dataFormat.getFormat("@"));
        csForTitleRequired.setDataFormat(dataFormat.getFormat("@"));
        //创建绘图对象
        Drawing<?> p = row.getSheet().createDrawingPatriarch();
        for (int i = 0; i < resultColumnList.size(); i++) {
            LimsResultBean result = resultColumnList.get(i);
            Cell cell = row.createCell(endCellNum + i);
            addComment(p, cell, result.getAnalyteId().toString());

            if (!StringUtils.isEmpty(result.getExt$Item("possibleresultinfo"))) {
                List<LimsPossibleResultBean> possibleResultBeans = JSONObject.parseArray(result.getExt$Item("possibleresultinfo"), LimsPossibleResultBean.class);
                possibleMap.put(endCellNum + i, possibleResultBeans);
            }
            analyteMap.put(endCellNum + i, result.getAnalyteId());
            String analyte = result.getAnalyte();
            if("D".equalsIgnoreCase(result.getAnalType())) {
                analyte += "(yyyy-MM-dd hh:mm)";
            }
            if (StringUtils.equals(result.getWriteFlag(), Constant.YES)) {
                cell.setCellValue(analyte + "*");
                cell.setCellStyle(csForTitleRequired);
            } else {
                cell.setCellValue(analyte);
                cell.setCellStyle(csForTitle);
            }
        }
    }

    private void addInfo(Sheet sheet, Workbook wb, List<LimsOrderTaskBean> orderTaskList, Map<Integer, Long> analyteMap, LimsRunBean runBean, List<LimsSampleBean> derivativeBeans, List<LimsResultBean> resultBeans, Map<Integer, List<LimsPossibleResultBean>> possibleMap) {
        CellStyle leftCellStyle = ExcelUtils.getLeftCellStyle(wb);
        leftCellStyle.setBorderTop(BorderStyle.THIN);
        DataFormat dataFormat = wb.createDataFormat();
        leftCellStyle.setDataFormat(dataFormat.getFormat("@"));
        int endRowNum = 6;
        //创建此表的Patriarch实例
        Drawing<?> patriarch = sheet.createDrawingPatriarch();

        CellStyle titleStyle = ExcelUtils.getTopicCellStyle(wb);
        titleStyle.setBorderBottom(BorderStyle.THIN);
        titleStyle.setBorderLeft(BorderStyle.THIN);
        titleStyle.setBorderRight(BorderStyle.THIN);
        titleStyle.setBorderTop(BorderStyle.THIN);
        setCellValue(sheet.getRow(2).getCell(0), runBean.getTestName().concat("-任务单"), titleStyle);
        sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 8));

        setCellValue(sheet.getRow(4).getCell(2), runBean.getRunCode(), leftCellStyle);
        if (!orderTaskList.isEmpty()) {
            setCellValue(sheet.getRow(3).getCell(2), runBean.getSubmitTime() != null ? DateTimeUtils.formatLocalDateTime(runBean.getSubmitTime()) : "", leftCellStyle);
            setCellValue(sheet.getRow(3).getCell(6), String.format("%s/%s", orderTaskList.get(0).getTester(), orderTaskList.get(0).getReviewer()), leftCellStyle);
            setCellValue(sheet.getRow(4).getCell(6), orderTaskList.get(0).getMethodDesc(), leftCellStyle);
        }

        List<LimsRunMaterialBean> materialBeans = limsRunMaterialService.selectListByFilter(SearchFilter.instance().match("runId", runBean.getId()).filter(MatchPattern.EQ));
        StringBuilder materials = new StringBuilder();
        for (int i = 0; i < materialBeans.size(); i++) {
            if (i != materialBeans.size() - 1) {
                materials.append(materialBeans.get(i).getMatCode()).append("-").append(materialBeans.get(i).getExt$Item("matname")).append(": \n");
            } else {
                materials.append(materialBeans.get(i).getMatCode()).append("-").append(materialBeans.get(i).getExt$Item("matname")).append(":");
            }
        }
        CellStyle cellStyle = ExcelUtils.getLeftCellStyle(wb);
        cellStyle.setWrapText(true);
//        setCellValue(sheet.getRow(5).getCell(2), materials.toString(), leftCellStyle);

        String[] orderTaskColumns = new String[]{"samplecode", "sampletype", "locations"};
        String[] derivativeGridColumns = new String[]{"sampleCode"};
        for (int i = 0; i < orderTaskList.size(); i++) {
            LimsOrderTaskBean orderTask = orderTaskList.get(i);
            Row row = sheet.createRow(i + endRowNum);
            Cell orderNoCell = row.createCell(0);
            orderNoCell.setCellStyle(leftCellStyle);
            orderNoCell.setCellValue(i + 1);
            int columnNum = 1;
            for (int j = 0; j < orderTaskColumns.length; j++) {
                Cell cell = row.createCell(j + columnNum);
                String columnName = orderTaskColumns[j];
                String value = ObjectUtils.toString(BeanUtils.getPropertyValue(orderTask, columnName));
                cell.setCellValue(value);
                cell.setCellStyle(leftCellStyle);
                if ("samplecode".equals(columnName)) {
                    addComment(patriarch, cell, orderTask.getId().toString());
                }
            }
            columnNum = columnNum + orderTaskColumns.length;
            for (int j = 0; j < derivativeGridColumns.length; j++) {
                Optional<LimsSampleBean> optionalLimsSampleBean = derivativeBeans.stream().filter(d -> d.getExt$Item("ordertaskid").contains(String.valueOf(orderTask.getId()))).findFirst();
                Cell cell = row.createCell(j + columnNum);
                String columnName = derivativeGridColumns[j];
                cell.setCellStyle(leftCellStyle);
                if (optionalLimsSampleBean.isPresent()) {
                    // ext$中的字段也可以用属性名直接获取（但注意批注是小写）
                    String value = ObjectUtils.toString(BeanUtils.getPropertyValue(optionalLimsSampleBean.get(), columnName));
                    cell.setCellValue(value);
                }
            }
            List<LimsResultBean> filterResults = resultBeans.stream().filter(r -> r.getOrderTaskId().equals(orderTask.getId())).collect(Collectors.toList());
            analyteMap.forEach((k, v) -> {
                Cell cell = row.createCell(k);
                Optional<LimsResultBean> optionalLimsResultBean = filterResults.stream().filter(r -> r.getAnalyteId().equals(v)).findFirst();
                if (optionalLimsResultBean.isPresent()) {
                    String value = ObjectUtils.toString(optionalLimsResultBean.get().getOriginalResult());
                    cell.setCellValue(value);
                    cell.setCellStyle(leftCellStyle);
                }
            });
        }        //设置下拉框
        possibleMap.keySet().forEach(cellNum -> {
            List<LimsPossibleResultBean> possibleResultBeans = possibleMap.get(cellNum);
            String[] strs = possibleResultBeans.stream().map(LimsPossibleResultBean::getOriginalResult).distinct().toArray(String[]::new);
            DataValidationHelper helper = sheet.getDataValidationHelper();
            DataValidationConstraint constraint = helper.createExplicitListConstraint(strs);
            //参数顺序：开始行、结束行、开始列、结束列
            CellRangeAddressList regions = new CellRangeAddressList(endRowNum, orderTaskList.size() + endRowNum - 1, cellNum, cellNum);
            DataValidation data = helper.createValidation(constraint, regions);
            data.setSuppressDropDownArrow(true);
            data.setShowErrorBox(true);
            sheet.addValidationData(data);
        });
    }

    private void setCellValue(Cell cell, String value, CellStyle cellStyle) {
        cell.setCellValue(value);
        cell.setCellStyle(cellStyle);
    }

    /**
     * 设置单元格注释
     *
     * @param patriarch
     * @param cell
     */
    public void addComment(Drawing patriarch, Cell cell, String note) {

        Comment comment = patriarch.createCellComment(new XSSFClientAnchor(0, 0, 0, 0, (short) 1, 2, (short) 4, 4));

        comment.setString(new XSSFRichTextString(note));

        cell.setCellComment(comment);

    }
}
